#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time    : 2021/7/19 14:23
# @Author  : Samge
import pymysql
from itkz.resources.db.mysql import MysqlBase
from itkz.resources.utils import common_util, date_util
from itkz.spider_ext import settings
from itkz.spider_ext.items.itkz import ItkzItem


class ItkzDb(MysqlBase):

    TABLE_NAME = 'ItkzData'
    SQL_CHECK = "select i_id from {table_name} where i_dataId={i_dataId} LIMIT 1"
    SQL_INSERT = "insert into {table_name}({keys}) Values({values})"
    SQL_UPDATE = "update {table_name} set {key_values} where i_dataId={i_dataId}"
    SQL_CHECK_DEFAULT = "SELECT * FROM {table_name} order by i_date desc LIMIT 20"
    # SQL_CHECK_ALL = "select i_dataId from {table_name} order by i_date desc"
    SQL_CHECK_ALL = "select i_dataId from {table_name} WHERE i_bd_txt is null or length(i_bd_txt)=0 order by i_date desc"

    def __init__(self, host=None, user=None, password=None, database=None):
        self.connect_db(database, host, password, user)

    def connect_db(self, database, host, password, user):
        self.log_info(f"{self.name()} 连接数据库：{database} {host} {password} {user}")
        host = host or settings.MYSQL_HOST
        user = user or settings.MYSQL_USER
        password = password or settings.MYSQL_PASSWORD
        database = database or settings.MYSQL_DATABASE
        self.log_info('{} {} {} {}'.format(host, user, password, database))
        self.conn = pymysql.connect(host=host, user=user, password=password, database=database, port=3307)
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.close_connect()

    def process_item(self, item, spider):
        super().process_item(item, spider)
        if isinstance(item, ItkzItem):
            if self.is_exist(item.get('i_dataId')):
                if self.need_update(spider):
                    self.update_item(item)
                else:
                    self.spider.log_info(f"该记录已经存在:{item.get('i_dataId')}\n")
            else:
                self.insert_item(item)
        return item

    def query(self, custom_sql):
        sql_str = custom_sql or self.SQL_CHECK_DEFAULT.format(table_name=self.TABLE_NAME)
        return self.ExecList(sql_str)

    def is_exist(self, i_dataId):
        select_sql = self.SQL_CHECK.format(table_name=self.TABLE_NAME, i_dataId=i_dataId)
        result = self.ExecList(select_sql)
        return result is not None and len(result) > 0

    def update_item(self, item):
        data = dict(item)
        if data.get('i_id'):
            del data['i_id']
        if data.get('i_dataId'):
            del data['i_dataId']
        data = common_util.remove_none_value_elements(data)
        key_values = ','.join(['%s=%r' % (col, data[col] or '') for col in data])
        update_sql = self.SQL_UPDATE.format(table_name=self.TABLE_NAME, key_values=key_values, i_dataId=item.get('i_dataId'))
        try:
            self.ExecNonQuery(update_sql)
            is_succeed = True
        except Exception as e:
            self.log_info(f"更新失败 {e}")
            is_succeed = False
        self.log_info(f"更新【{is_succeed}】: {item.get('i_dataId')}")
        return is_succeed

    def insert_item(self, item):
        if self.is_exist(item.get('i_dataId')):
            self.log_info(f"数据已存在，跳过：【{item.get('i_dataId')}】")
            return
        curr_date = date_util.get_today_str()
        item['i_date_add'] = curr_date
        item['i_date_update'] = curr_date
        item['i_status'] = item.get('i_status') if item.get('i_status') is not None else 1
        data = dict(item)
        data = common_util.remove_none_value_elements(data)
        insert_sql = self.SQL_INSERT.format(table_name=self.TABLE_NAME,
                                            keys=','.join(data.keys()),
                                            values=','.join(["'%s'" % col for col in data.values()])
                                            )
        try:
            self.ExecNonQuery(insert_sql)
            is_succeed = True
        except Exception as e:
            self.log_info(f"插入失败 {e}")
            is_succeed = False
        self.log_info(f"数据入库【{is_succeed}】 {item.get('i_dataId')}")
        return is_succeed

    def get_all_dataId(self):
        return self.ExecList(self.SQL_CHECK_ALL.format(table_name=self.TABLE_NAME))
